#!/usr/bin/python    
#encoding=utf-8  

import MySQLdb
import json  
import struct
import app.service.door.services.comm as comm

class DB_MySQL:
    def __init__(self):
        # self.MySQL_HOST='192.168.1.243'
        # self.MySQL_USER='root'
        # self.MySQL_PASSWD='123456'
        self.MySQL_HOST='127.0.0.1'
        self.MySQL_USER='fy'
        self.MySQL_PASSWD='fy0123498765'
        self.MySQL_PORT=3306
        self.MySQL_DB='imcmdb'
    
    def build_Conn(self):
	try:
            conn=MySQLdb.connect(
	    host=self.MySQL_HOST,
	    port=self.MySQL_PORT,
	    user=self.MySQL_USER,
	    passwd=self.MySQL_PASSWD,
	    db=self.MySQL_DB,
	    charset='utf8'
	    )
	    return conn
	except MySQLdb.Error,e:
	    print "Mysql Error %d: %s" % (e.args[0], e.args[1])
            return False
    def build_Conn_2(self):
	try:
            conn=MySQLdb.connect(
	    host=self.MySQL_HOST,
	    port=self.MySQL_PORT,
	    user=self.MySQL_USER,
	    passwd=self.MySQL_PASSWD,
	    db="imhdb",
	    charset='utf8'
	    )
	    return conn
	except MySQLdb.Error,e:
	    print "Mysql Error %d: %s" % (e.args[0], e.args[1])
            return False

    def Get_Cards(self):
        try:
	    print "start"
	    conn=self.build_Conn()
	    print "ok"
	    cur=conn.cursor()
            strSql='select card_no,user_name,user_pwd,group_no from d_user '
	    print strSql
	    cur.execute(strSql)	 
	    results=cur.fetchall()
	    cur.close()
	    conn.close()
	    return results
	except MySQLdb.Error,e:
	    print "Mysql Error %d: %s" % (e.args[0], e.args[1])
            return False  


    def Get_CardByNo(self,_card):
        try:
	    conn=self.build_Conn()
	    cur=conn.cursor()
            strSql='select card_no,user_name,user_pwd,group_no from t_door_card_ex where card_no="{0}"'.format(_card)
	    # print strSql
	    cur.execute(strSql)	 
	    result=cur.fetchone()
	    cur.close()
	    conn.close()
	    return result
	except MySQLdb.Error,e:
	    print "Mysql Error %d: %s" % (e.args[0], e.args[1])
            return False

    def Get_CardByNoId(self,_id):
        try:
	    conn=self.build_Conn()
	    cur=conn.cursor()
            strSql='select card_no,user_name,user_pwd,group_no from t_door_card_ex where id="{0}"'.format(_id)
	    # print strSql
	    cur.execute(strSql)
	    result=cur.fetchone()
	    cur.close()
	    conn.close()
	    return result
	except MySQLdb.Error,e:
	    print "Mysql Error %d: %s" % (e.args[0], e.args[1])
            return False  

    def card_ins(self,cardNo,userName,userPwd,groupNo):
	try:
	    conn=self.build_Conn()
	    cur=conn.cursor()
	    strSql='replace into t_door_card_ex(card_no,user_name,user_pwd,group_no) values("{0}","{1}","{2}","{3}")'.format(cardNo,userName,userPwd,groupNo)
	    #print strSql
	    cur.execute(strSql)
	    conn.commit()
	    cur.close()
	    conn.close()
	    #print 'count:{0}'.format(user_id)
	    return True
        except MySQLdb.Error,e:
	    print "Mysql Error %d: %s" % (e.args[0], e.args[1])
            return False  


    def event_ins(self,datas):
	try:
	    conn=self.build_Conn_2()
	    cur=conn.cursor()
	    strSql='insert into t_event(alarmid,ciid,configid,begintime,alarmlevel,oldvalue,alarmvalue,content,ciname,devid) \
values("{0}","{1}","{2}",{3},{4},"{5}","{6}","{7}","{8}",{9})'.format(datas['alarmid'],datas['ciid'],datas['configid'],datas['begintime'],datas['alarmlevel'],datas['oldvalue'],datas['alarmvalue'],datas['content'],datas['ciname'],datas['devid'])
	    cur.execute(strSql)
	    conn.commit()
	    cur.close()
	    conn.close()
	    #print 'count:{0}'.format(user_id)
	    return True
        except MySQLdb.Error,e:
	    print "Mysql Error %d: %s" % (e.args[0], e.args[1])
            return False  

    def card_upd(self,_id,cardNo,userName,userPwd,groupNo):
	try:
	    conn=self.build_Conn()
	    cur=conn.cursor()
	    strSql='update t_door_card_ex set card_no="{0}",user_name="{1}",user_pwd="{2}",group_no="{3}" where id={4}'.format(cardNo,userName,userPwd,groupNo,_id)
	    # print strSql
	    cur.execute(strSql)
	    conn.commit()
	    cur.close()
	    conn.close()
	    #print 'count:{0}'.format(user_id)
	    return True
        except MySQLdb.Error,e:
	    print "Mysql Error %d: %s" % (e.args[0], e.args[1])
            return False  



    def Get_CardCount(self):
	try:
	    conn=self.build_Conn()
	    cur=conn.cursor()
	    strSql='select count(*) as card_count from t_door_card_ex'
	    cur.execute(strSql)
	    re = cur.fetchone()
	    cur.close()
	    conn.close()
	    return re[0]
        except MySQLdb.Error,e:
	    print "Mysql Error %d: %s" % (e.args[0], e.args[1])
            return False  


    def Get_CardLists(self,page,page_size):
	try:
	    conn=self.build_Conn()
	    cur=conn.cursor()
	    statr_num = (int(page) - 1) * int(page_size)
	    strSql='select * from t_door_card_ex order by id desc limit {0},{1} '.format(int(statr_num), int(page_size))
	    cur.execute(strSql)
	    re = cur.fetchall()
	    cur.close()
	    conn.close()
	    return re
        except MySQLdb.Error,e:
	    print "Mysql Error %d: %s" % (e.args[0], e.args[1])
            return False  


    def Delete_CardByNo(self,card_no):
        try:
	    conn=self.build_Conn()
	    cur=conn.cursor()
            strSql='delete from t_door_card_ex where card_no="{0}"'.format(card_no)
	    cur.execute(strSql)	 
	    conn.commit()
	    cur.close()
	    conn.close()
	    return True
	except MySQLdb.Error,e:
	    print "Mysql Error %d: %s" % (e.args[0], e.args[1])
            return False 

